﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.SqlClient;
using MDL;

namespace DAL
{
    public class DAL_ConsumerBill
    {
        /// <summary>
        /// 获取当日最新账单编号
        /// </summary>
        /// <returns>最新账单编号</returns>
        public static string GetMaxCBID() {
            string sql = "select max(CBID) from ConsumerBill where CBID like @CBID";
            SqlParameter[] sprs = { 
                                 new SqlParameter("@CBID","ZD"+DateTime.Now.ToString("yyyyMMdd")+"%")
                                 };
            object obj = DBHelper.单值(sql, sprs, CommandType.Text);
            return obj == null ? "" : obj.ToString();
        }
        /// <summary>
        /// 右键修改状态为可用时改消费为结账
        /// </summary>
        /// <param name="id">餐台ID</param>
        /// <param name="CBID">账单号</param>
        /// <returns>受影响的行数</returns>
        public static int UpdateZT(int id,string CBID)
        {
            string sql = @"Update ConsumerBill set CBClose = 1 where
                        CBClose =0 and TableID=@id and CBID= @CBID";
            SqlParameter[] sprs = { 
                                    new SqlParameter("@id",id),
                                    new SqlParameter("@CBID",CBID),
                                 };
            return DBHelper.增删改(sql, sprs, CommandType.Text);
        }
        /// <summary>
        /// 顾客开单的方法
        /// </summary>
        /// <param name="cb">开单信息</param>
        /// <returns>受影响的行数</returns>
        public static int AddConsumerBill(ConsumerBill cb) {
            string sql = @"insert into ConsumerBill values(@CBID,@TableID,@CBAmount,null,1,getdate(),null,@AdminID,null,0)";
            SqlParameter[] sprs = { 
                                    new SqlParameter("@CBID",cb.CBID),
                                    new SqlParameter("@TableID",cb.TableID),
                                    new SqlParameter("@CBAmount",cb.CBAmount),
                                    new SqlParameter("@AdminID",cb.AdminID)
                                 };
            return DBHelper.增删改(sql, sprs, CommandType.Text);
        }
        /// <summary>
        /// 根据餐桌ID获取其当前账单编号
        /// </summary>
        /// <param name="TableID">餐桌ID</param>
        /// <returns>账单编号</returns>
        public static string GetMaxCBID(int TableID)
        {
            string sql = "select max(CBID) from ConsumerBill where TableID = @TableID";
            SqlParameter[] sprs = { 
                                 new SqlParameter("@TableID",TableID)
                                 };

            object obj = DBHelper.单值(sql, sprs, CommandType.Text);
            return obj == null ? "" : obj.ToString();
        }
        /// <summary>
        /// 根据餐桌ID查询开单时间
        /// </summary>
        /// <param name="TableID">餐桌ID</param>
        /// <returns>开单时间</returns>
        public static object GetStateTime(int TableID)
        {
            string sql = "select top 1 CBStartDate from ConsumerBill where TableID = @TableID and CBClose = 0 order by CBID desc";
            SqlParameter[] sprs = { new SqlParameter ("@TableID",TableID)};
            return DBHelper.单值(sql,sprs,CommandType.Text);
        }
        /// <summary>
        /// 查询最早开单时间
        /// </summary>
        /// <returns></returns>
        public static Object SelectMinTime()
        {
            string sql = "select MIN(CBStartDate) from ConsumerBill";
            return DBHelper.单值(sql, null, CommandType.Text);
        }
        /// <summary>
        /// 查询账单信息
        /// </summary>
        /// <param name="ktime">开单时间</param>
        /// <param name="jtime">结账时间</param>
        /// <param name="Rid">收款人</param>
        /// <param name="Tid">餐台ID</param>
        /// <returns>账单信息</returns>
        public static List<ConsumerBill> SelectInfo(string ktime, string jtime, int Rid,string Tid)
        {
            string sql = @"select * from ConsumerBill c inner join Tables t on c.TableID = t.TableID where CBClose = 1 ";
            List<SqlParameter> sprs = new List<SqlParameter>();
            if (!ktime.Equals("") && !jtime.Equals(""))
            {
                sql += " and CBEndDate between @ktime and @jtime";
                sprs.Add(new SqlParameter("@ktime",ktime));
                sprs.Add(new SqlParameter("@jtime", jtime));
            }
            if (Rid > 0)
            {
                sql += " and AdminID = @Rid";
                sprs.Add(new SqlParameter("@Rid",Rid));
            }
            if (!Tid.Trim().Equals(""))
            {
                sql += " and t.TableName like @Tid";
                sprs.Add(new SqlParameter("@Tid", "%"+Tid+"%"));
            }
            List<ConsumerBill> list = new List<ConsumerBill>();
            using (SqlDataReader dr = DBHelper.白道(sql,sprs.ToArray(),CommandType.Text))
            {
                while (dr.Read())
                {
                    ConsumerBill c = new ConsumerBill();
                    c.CBID = dr[0].ToString(); ;
                    c.TableID = Convert.ToInt32(dr[1]);
                    c.CBEndDate = dr[6].ToString();
                    //消费金额
                    c.CBSale = Convert.ToDouble(dr[8]);
                    //优惠金额
                    c.CDSale = c.CBSale * (1 - Convert.ToDouble(dr[4]));
                    //实用金额
                    c.CDMoney = c.CBSale - c.CDSale;
                    list.Add(c);
                }
            }
            return list;
        }
        /// <summary>
        /// 会员结账的方法
        /// </summary>
        /// <param name="cb">账单信息</param>
        /// <returns></returns>
        public static int EndConsumerBillVip(ConsumerBill cb)
        {
            string sql = @"update ConsumerBill set VipID=@VipID,CBDiscount=@CBDiscount,
                        CBEndDate=GETDATE(),CBSale=@CBSale,CBClose=1 where CBID=@CBID";
            SqlParameter[] sprs = { 
                                 new SqlParameter("@VipID",cb.VipID),
                                 new SqlParameter("@CBDiscount",cb.CBDiscount),
                                 new SqlParameter("@CBSale",cb.CBSale),
                                 new SqlParameter("@CBID",cb.CBID)
                                 };
            return DBHelper.增删改(sql, sprs, CommandType.Text);
        }
        /// <summary>
        ///普通结账的方法
        /// </summary>
        /// <param name="cb">账单信息</param>
        /// <returns></returns>
        public static int EndConsumerBillPt(ConsumerBill cb)
        {
            string sql = @"update ConsumerBill set 
                        CBEndDate=GETDATE(),CBSale=@CBSale,CBClose=1 where CBID=@CBID";
            SqlParameter[] sprs = { 
                                 new SqlParameter("@CBSale",cb.CBSale),
                                 new SqlParameter("@CBID",cb.CBID)
                                 };
            return DBHelper.增删改(sql, sprs, CommandType.Text);
        }
    }
}
